
// const logger = require('../lib/logger.js')
const debug = require('debug')('db')

const sqlite3 = require('sqlite3').verbose()
const ConfigManager = require('../lib/config')
const file = ConfigManager.getDbFile()
const db = new sqlite3.Database(file)

function DB () {
}

DB.prototype = {
  constructor: DB,
  // 查询微信登录用户
  findUserId: async function (openid) {
    return new Promise((resolve, reject) => {
      db.all('select * from User where id = ?', openid, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          return resolve(row)
        }
      })
    })
  },

  // 保存用户
  savaUser: async function (newUser) {
    // 存储到数据库
    return new Promise((resolve, reject) => {
      const sql = db.prepare('insert into User(id, name, avatar) values(?, ?, ?)', newUser.id, newUser.name, newUser.avatar)
      sql.run()
      return resolve('插入成功')
    })
  },

  // 修改用户文件个数+1
  updateUserFile: async function (userid) {
    return new Promise((resolve, reject) => {
      const sql = db.prepare('update User set fileNumber = fileNumber + 1 where id = ?', userid)
      sql.run()
      return resolve('修改成功')
    })
  },

  // 修改用户打印次数+1
  updateUser: async function (userid) {
    return new Promise((resolve, reject) => {
      const sql = db.prepare('update User set printNumber = printNumber + 1 where id = ?', userid)
      sql.run()
      return resolve('修改成功')
    })
  },

  // 模糊查询用户
  findUserLike: async function (name) {
    return new Promise((resolve, reject) => {
      const sql = "Select count(*) as num, * from User where name LIKE '%" + name + "%'"
      db.all(sql, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 查询后台管理系统分页用户
  findUser: function (page, pageSize) {
    return new Promise((resolve, reject) => {
      db.all('select * from User limit ? OFFSET ?', pageSize, page, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 查询用户总数
  findUserCount: function () {
    return new Promise((resolve, reject) => {
      db.all('select count(*) as num from User', function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 查询所有用户打印总数
  findTotolPrintNumber: function () {
    return new Promise((resolve, reject) => {
      db.all('select sum(printNumber) as totolPrintNumber from User', function (err, row) {
        if (err) {
          return reject(err)
        } else {
          return resolve(row)
        }
      })
    })
  },

  // 查询打印次数最多的top10用户
  findPrintNumber: function () {
    return new Promise((resolve, reject) => {
      db.all('select name, printNumber from User order by printNumber desc limit 0,8', function (err, row) {
        if (err) {
          return reject(err)
        } else {
          return resolve(row)
        }
      })
    })
  },

  // 查找某个文件是否上传成功
  findFilestat: function (fileUuid) {
    return new Promise((resolve, reject) => {
      db.all('select * from File where fileUuid = ?', fileUuid, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 查找某个用户个人所有文件
  findUserFile: function (userId) {
    return new Promise((resolve, reject) => {
      db.all('select * from File where userId = ?', userId, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 文件操作
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------

  // 查询文件总数
  findFileCount: function () {
    return new Promise((resolve, reject) => {
      db.all('select count(*) as num from File', function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 模糊查询文件
  findFileLike: async function (name) {
    return new Promise((resolve, reject) => {
      const sql = "Select count(*) as num, * from File where fileName LIKE '%" + name + "%'"
      db.all(sql, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 查找后台管理系统分页文件
  findFile: function (page, pageSize) {
    return new Promise((resolve, reject) => {
      db.all('select * from File limit ? OFFSET ?', pageSize, page, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 保存文件
  savaFile: async function (newFile) {
    // 存储到数据库
    return new Promise((resolve, reject) => {
      const sql = db.prepare('insert into File(fileUuid, userId, userName, filePage, fileName, size, type) values(?, ?, ?, ?, ?, ?, ?)', newFile.fileUuid, newFile.userId, newFile.userName, newFile.filePage, newFile.fileName, newFile.size, newFile.type)
      sql.run()
      return resolve('插入成功')
    })
  },

  // 删除文件
  deleteFile: async function (fileUuid) {
    return new Promise((resolve, reject) => {
      const sql = db.prepare('delete from File where fileUuid = ?', fileUuid)
      sql.run()
      return resolve('删除成功')
    })
  },

  // 打印机操作
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------

  // 查询某台打印机
  findPrintId: async function (printerUuid) {
    return new Promise((resolve, reject) => {
      db.all('select * from Print where printerUuid = ?', printerUuid, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成')
          return resolve(row)
        }
      })
    })
  },

  // 查询打印机总数
  findPrintCount: function () {
    return new Promise((resolve, reject) => {
      db.all('select count(*) as num from Print', function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 查找后台管理系统打印机分页
  findPrints: function (page, pageSize) {
    return new Promise((resolve, reject) => {
      db.all('select * from Print limit ? OFFSET ?', pageSize, page, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 保存打印机
  savaPrint: async function (newPrint) {
    // 存储到数据库
    return new Promise((resolve, reject) => {
      const sql = db.prepare('insert into Print values(?, ?, ?, ?, ?, ?, ?)', newPrint.printerUuid, newPrint.bid, newPrint.deviceid, newPrint.printName, newPrint.Brand, newPrint.model, newPrint.status)
      sql.run()
      return resolve('插入成功')
    })
  },

  // 修改打印机
  updatePrint: async function (newPrint) {
    return new Promise((resolve, reject) => {
      const sql = db.prepare('update Print set bid = ?, printName = ?, brand = ?, model = ? where deviceid = ?', newPrint.bid, newPrint.printName, newPrint.Brand, newPrint.model, newPrint.deviceid)
      sql.run()
      return resolve('修改成功')
    })
  },

  // 删除打印机
  deletePrint: async function (printerUuid) {
    return new Promise((resolve, reject) => {
      const sql = db.prepare('delete from Print where printerUuid = ?', printerUuid)
      sql.run()
      return resolve('删除成功')
    })
  },

  // 打印记录操作
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------

  // 查询近7日打印数
  findRecordNumbers: function () {
    // const date = new Date().toISOString().replace(/T/, ' ').replace(/\..+/, '')
    // "select count(*) as num from Record where date >= datetime('now', 'start of day', '-6 day') group by date order by date asc"
    return new Promise((resolve, reject) => {
      const sql = ("select strftime('%Y-%m-%d', date) as dates, count(*) as num from Record where date >= datetime('now', 'start of day', '-6 day') group by strftime('%Y-%m-%d', date) order by strftime('%Y-%m-%d', date)")
      db.all(sql, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 查询打印记录总数
  findRecordCount: function () {
    return new Promise((resolve, reject) => {
      db.all('select count(*) as num from Record', function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 查找后台打印机记录分页
  findRecords: function (page, pageSize) {
    return new Promise((resolve, reject) => {
      db.all('select rowid,* from Record limit ? OFFSET ?', pageSize, page, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 保存打印记录
  savaRecord: async function (user, fileName, status) {
    // 存储到数据库
    return new Promise((resolve, reject) => {
      const sql = db.prepare('insert into record(user, fileName, status) values(?, ?, ?)', user, fileName, status)
      sql.run()
      return resolve('插入成功')
    })
  },

  // 管理员操作
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------
  // ---------------------------------------------------------------------------------------------

  // 查询后台管理系统管理员分页
  findAdminUser: function (page, pageSize) {
    return new Promise((resolve, reject) => {
      db.all('select rowid,* from AdminUser limit ? OFFSET ?', pageSize, page, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 查询管理员总数
  findAdminCount: function () {
    return new Promise((resolve, reject) => {
      db.all('select count(*) as num from AdminUser', function (err, row) {
        if (err) {
          return reject(err)
        } else {
          debug('查找完成', row)
          return resolve(row)
        }
      })
    })
  },

  // 查询某个管理员
  findAdmin: async function (adminName, password) {
    return new Promise((resolve, reject) => {
      db.all('select * from AdminUser where name = ? and password = ?', adminName, password, function (err, row) {
        if (err) {
          return reject(err)
        } else {
          return resolve(row)
        }
      })
    })
  },

  // 修改管理员密码
  updatepassword: function (adminId, newpassword) {
    return new Promise((resolve, reject) => {
      const sql = db.prepare('update AdminUser set password = ? where adminId = ?', newpassword, adminId)
      sql.run()
      return resolve('修改成功')
    })
  },

  // 保存管理员
  savaAdmin: async function (newAdmin) {
    // 存储到数据库
    return new Promise((resolve, reject) => {
      const sql = db.prepare('insert into AdminUser(AdminId, name, password) values(?, ?, ?)', newAdmin.adminId, newAdmin.name, newAdmin.password)
      sql.run()
      return resolve('插入成功')
    })
  },

  // 修改管理员
  updateAdmin: async function (newAdmin) {
    return new Promise((resolve, reject) => {
      const sql = db.prepare('update AdminUser set name = ?, password = ? where adminId = ?', newAdmin.name, newAdmin.password, newAdmin.adminId)
      sql.run()
      return resolve('修改成功')
    })
  },

  // 删除管理员
  deleteAdmin: async function (adminId) {
    return new Promise((resolve, reject) => {
      const sql = db.prepare('delete from AdminUser where adminId = ?', adminId)
      sql.run()
      return resolve('删除成功')
    })
  }

}

module.exports = new DB()
